﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _同一SqlCommand下使用多个参数
{
    class Program
    {
        static void Main(string[] args)
        {
            //string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["Default"].ToString();
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    conn.Open();
            //    SqlTransaction tran = conn.BeginTransaction();
            //    using (SqlCommand cmd = new SqlCommand())
            //    {
            //        try
            //        {
            //            //插入Product表数据
            //            string sql = "INSERT INTO dbo.Product ( Name, RenQi, InputTime ) VALUES ( @Name, @RenQi, GETDATE())";
            //            cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 128);
            //            cmd.Parameters.Add("@RenQi", SqlDbType.Int);
            //            cmd.Parameters["@Name"].Value = "筱筱";
            //            cmd.Parameters["@RenQi"].Value = 99;
            //            cmd.Transaction = tran;
            //            cmd.Connection = conn;
            //            cmd.CommandText = sql;
            //            cmd.CommandType = CommandType.Text;
            //            cmd.ExecuteNonQuery();
            //            tran.Commit();
            //        }
            //        catch (Exception ex)
            //        {
            //            string exMessage = ex.Message.ToString();
            //            tran.Rollback();
            //            throw ex;
            //        }
            //    }
            //}



            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["Default"].ToString();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        //插入Product表数据
                        string sql = "INSERT INTO dbo.Product ( Name, RenQi, InputTime ) VALUES ( @Name, @RenQi, GETDATE())";
                        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 128);
                        cmd.Parameters.Add("@RenQi", SqlDbType.Int);
                        cmd.Parameters["@Name"].Value = "筱筱";
                        cmd.Parameters["@RenQi"].Value = 99;
                        cmd.Transaction = tran;
                        cmd.Connection = conn;
                        cmd.CommandText = sql;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();

                        //插入PersonData表数据
                        sql = "INSERT dbo.PersonData ( OrderId , PersonCode , Score , InputTime ) VALUES ( @OrderId , @PersonCode , @Score , GETDATE() )";
                        cmd.Parameters.Add("@OrderId", SqlDbType.NVarChar, 128);
                        cmd.Parameters.Add("@PersonCode", SqlDbType.NVarChar, 128);
                        cmd.Parameters.Add("@Score", SqlDbType.Int);

                        cmd.Parameters["@OrderId"].Value = "11";
                        cmd.Parameters["@PersonCode"].Value = "小小";
                        cmd.Parameters["@Score"].Value = 99;

                        cmd.Transaction = tran;
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                        tran.Commit();

                    }
                    catch (Exception ex)
                    {
                        string exMessage = ex.Message.ToString();
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}
